<?php

class Semester_CopyController extends Zend_Controller_Action
{

    public function init()
    {
        /* Initialize action controller here */
    }

    public function indexAction()
    {
        // action body
    }

    /**
     * 複製資料
     * @return unknown_type
     */
    public function copyAction()
    {
        $year= $this->view->Semester['year'] ;
        $semester = $this->view->Semester['semester'];
        // 篩選傳入值
        $filters = array('*' => 'StringTrim');
        $filterParam = new Zend_Filter_Input($filters,array(),$this->getRequest()->getParams());
        $yearSemester = $filterParam->semester;
        $temp = explode("-", $yearSemester);
        $sYear = $temp[0];
        $sSemester = $temp[1];
        $act = $filterParam->act;
        switch ($act) {
            case 'class_set':
                $module = 'Semester_Model_SemesterClass';
                $q = Doctrine_Query::create()
                ->from($module)
                ->where('year=?', $sYear)
                ->andWhere('semester=?', $sSemester)
                ->andWhere('enable=?','Y');
                foreach($q->fetchArray() as $row) {
                    $m = new $module;
                    unset($row['sn']);
                    $row['year'] = $year;
                    $row['semester'] = $semester;
                    $m->fromArray($row);
                    $m->save();
                }
                break;
            case 'score_setup':
                $module = 'Semester_Model_SemesterExamine';
                $q = Doctrine_Query::create()
                ->from($module)
                ->where('year=?', $sYear)
                ->andWhere('semester=?', $sSemester)
                ->andWhere('enable=?','Y');
                foreach($q->fetchArray() as $row) {
                    $m = new $module;
                    unset($row['sn']);
                    $row['year'] = $year;
                    $row['semester'] = $semester;
                    $m->fromArray($row);
                    $m->save();
                }
                break;
            case 'course_setup' :
                $module = 'Semester_Model_SemesterCourse';
                $subjectArr = array();
                $q = Doctrine_Query::create()
                ->from($module)
                ->where('year=?', $sYear)
                ->andWhere('semester=?', $sSemester)
                ->andWhere('enable=?','Y');
                foreach($q->fetchArray() as $row) {
                    $m = new $module;
                    $oldSn = $row['sn'];
                    unset($row['sn']);
                    $row['year'] = $year;
                    $row['semester'] = $semester;
                    $m->fromArray($row);
                    $m->save();
                    $subjectArr[$oldSn] = $m->sn;
                }
                //print_r($subjectArr); exit;
                $module = 'Semester_Model_SemesterSubject';
                $q = Doctrine_Query::create()
                ->from($module)
                ->where('year=?', $sYear)
                ->andWhere('semester=?', $sSemester)
                ->andWhere('enable=?','Y');
        //        print_r($q->fetchArray()) ; exit;
                foreach($q->fetchArray() as $row) {
                    $newSubjectSn = $subjectArr[$row['subject_sn']];
                    $m = new $module;
                    unset($row['sn']);
                    $row['year'] = $year;
                    $row['semester'] = $semester;
                    $row['subject_sn'] = $newSubjectSn;
      //              print_r($row);
                   $m->fromArray($row);
                    $m->save();
                }
                break;
                //節次設定
            case 'section_setup':
                $module = 'Semester_Model_SemesterSection';
                // 先找這學期 subject_sn 值
                $res = Doctrine_Query::create()
                ->select('sn, grade, scope, name')
                ->from('Semester_Model_SemesterCourse')
                ->where('year=?' ,$year)
                ->andWhere('semester=?', $semester)
                ->fetchArray();
                $tempArr = array();
                foreach ($res as $row)
                $tempArr[$row['grade']][$row['scope']][$row['name']] = $row['sn'];

                $subjectArr = array();
                // 建立 上學期 與 這學期 subject_sn 的對應
                $res = Doctrine_Query::create()
                ->select('sn, grade, scope, name')
                ->from('Semester_Model_SemesterCourse')
                ->where('year=?' ,$sYear)
                ->andWhere('semester=?', $sSemester)
                ->fetchArray();
                foreach ($res as $row)
                $subjectArr[$row['sn']] = $tempArr[$row['grade']][$row['scope']][$row['name']];

                $sectionArr = array();

                $q = Doctrine_Query::create()
                ->from($module)
                ->where('year=?', $sYear)
                ->andWhere('semester=?', $sSemester)
                ->andWhere('enable=?','Y');
                foreach($q->fetchArray() as $row) {
                    $m = new $module;
                    $oldSn = $row['sn'];
                    unset($row['sn']);
                    $row['year'] = $year;
                    $row['semester'] = $semester;
                    $m->fromArray($row);
                    $m->save();
                    $sectionArr[$oldSn] = $m->sn;
                }

                // 開始複製
                $q = Doctrine_Query::create()
                ->select('a.*')
                ->from('Semester_Model_SemesterTimetable a')
                ->leftJoin('a.Semester_Model_SemesterSection b')
                ->where('b.year=?', $sYear)
                ->andWhere('b.semester=?', $sSemester);
                foreach($q->fetchArray() as $row) {
                    $m = new Semester_Model_SemesterTimetable();
                    unset($row['sn']);
                    $row['subject_sn'] = $subjectArr[$row['subject_sn']];
                    $row['section_sn'] = $sectionArr[$row['section_sn']];
                    $m->fromArray($row);
                    $m->save();
                }

                break;

                // 教師配課設定
            case 'teacher_setup':
                $module = 'Semester_Model_SemesterTeacherCourse';
                // 先找這學期 subject_sn 值
                $res = Doctrine_Query::create()
                ->select('sn, grade, scope, name')
                ->from('Semester_Model_SemesterCourse')
                ->where('year=?' ,$year)
                ->andWhere('semester=?', $semester)
                ->fetchArray();
                $tempArr = array();
                foreach ($res as $row)
                $tempArr[$row['grade']][$row['scope']][$row['name']] = $row['sn'];

                $subjectArr = array();
                // 建立 上學期 與 這學期 subject_sn 的對應
                $res = Doctrine_Query::create()
                ->select('sn, grade, scope, name')
                ->from('Semester_Model_SemesterCourse')
                ->where('year=?' ,$sYear)
                ->andWhere('semester=?', $sSemester)
                ->fetchArray();
                foreach ($res as $row)
                $subjectArr[$row['sn']] = $tempArr[$row['grade']][$row['scope']][$row['name']];

                $q = Doctrine_Query::create()
                ->from($module)
                ->where('year=?', $sYear)
                ->andWhere('semester=?', $sSemester);
                foreach($q->fetchArray() as $row) {
                    $m = new $module;
                    unset($row['sn']);
                    $row['year'] = $year;
                    $row['semester'] = $semester;
                    $row['subject_sn'] = $subjectArr[$row['subject_sn']];
                    $m->fromArray($row);
                    $m->save();
                }

                break;


            case 'timetable_setup':
                $module = 'Semester_Model_SemesterTimetable';
                // 先找這學期 subject_sn 值
                $res = Doctrine_Query::create()
                ->select('sn, grade, scope, name')
                ->from('Semester_Model_SemesterCourse')
                ->where('year=?' ,$year)
                ->andWhere('semester=?', $semester)
                ->fetchArray();
                $tempArr = array();
                foreach ($res as $row)
                $tempArr[$row['grade']][$row['scope']][$row['name']] = $row['sn'];

                $subjectArr = array();
                // 建立 上學期 與 這學期 subject_sn 的對應
                $res = Doctrine_Query::create()
                ->select('sn, grade, scope, name')
                ->from('Semester_Model_SemesterCourse')
                ->where('year=?' ,$sYear)
                ->andWhere('semester=?', $sSemester)
                ->fetchArray();
                foreach ($res as $row)
                $subjectArr[$row['sn']] = $tempArr[$row['grade']][$row['scope']][$row['name']];

                //  先找到這學期的 section
                $res = Doctrine_Query::create()
                ->select('grade, sn')
                ->from('Semester_Model_SemesterSection')
                ->where('year=?', $year)
                ->andWhere('semester=?', $semester)
                ->fetchArray();
                //如果這學期沒有資料具回傳 false
                if (count($res) ==0)
                $this->_helper->json(false);
                $tempArr = array();
                foreach($res as $row)
                $tempArr[$row['grade']][$row['sort']] = $row['sn'];

                $sectionArr = array();
                // 建立 上學期 與 這學期 section_sn 的對應
                $res = Doctrine_Query::create()
                ->select('grade, sn')
                ->from('Semester_Model_SemesterSection')
                ->where('year=?' ,$sYear)
                ->andWhere('semester=?', $sSemester)
                ->fetchArray();
                foreach ($res as $row)
                $sectionArr[$row['sn']] = $tempArr[$row['grade']][$row['sort']];

                // 開始複製
                $q = Doctrine_Query::create()
                ->select('a.*')
                ->from('Semester_Model_SemesterTimetable a')
                ->leftJoin('a.Semester_Model_SemesterSection b')
                ->where('b.year=?', $sYear)
                ->andWhere('b.semester=?', $sSemester);
                foreach($q->fetchArray() as $row) {
                    $m = new $module;
                    unset($row['sn']);
                    $row['subject_sn'] = $subjectArr[$row['subject_sn']];
                    $row['section_sn'] = $sectionArr[$row['section_sn']];
                    $m->fromArray($row);
                    $m->save();
                }


                break;
            case 'tutor_setup':
                $module = 'Semester_Model_SemesterTeacher';
                $q = Doctrine_Query::create()
                ->from($module)
                ->where('year=?', $sYear)
                ->andWhere('semester=?', $sSemester);
                foreach($q->fetchArray() as $row) {
                    $m = new $module;
                    unset($row['sn']);
                    $row['year'] = $year;
                    $row['semester'] = $semester;
                    $m->fromArray($row);
                    $m->save();
                }
                break;
        }
        $this->_helper->json(true);
    }

    /**
     * 刪除資料
     * @return unknown_type
     */
    public function deleteAction()
    {
        $year= $this->view->Semester['year'] ;
        $semester = $this->view->Semester['semester'];
        // 篩選傳入值
        $filters = array('*' => 'StringTrim');
        $filterParam = new Zend_Filter_Input($filters,array(),$this->getRequest()->getParams());
        $act = $filterParam->act;
        $module = '';
        switch ($act) {
            case 'class_set':
                $module = 'Semester_Model_SemesterClass';
                break;
            case 'score_setup':
                $module = 'Semester_Model_SemesterExamine';
                break;
            case 'course_setup' :
                $module = 'Semester_Model_SemesterCourse';
                break;
            case 'section_setup':
                $module = 'Semester_Model_SemesterSection';
                break;
            case 'teacher_setup':
                $module = 'Semester_Model_SemesterTeacherCourse';
                break;
            case 'timetable_setup':
                //               $q =  Doctrine_Query::create()
                //                ->delete('Semester_Model_SemesterTimetable')
                //                ->whereIN('section_sn',
                //                "SELECT sn FROM Semester_Model_SemesterSection WHERE year=$year AND semester=$semester')");
                //                //echo $q->getSqlQuery();
                //                 echo $q->execute();

                //->andWHere('semester=?', $semester)
                break;
            case 'tutor_setup':
                $module = 'Semester_Model_SemesterTeacher';
                break;
        }
        if ($module) {
            Doctrine_Query::create()
            ->delete($module)
            ->where('year=?', $year)
            ->andWHere('semester=?', $semester)
            ->execute();
        }
        $this->_helper->json(true);
    }

    /**
     * 取得學期及資料總數
     * @return unknown_type
     */
    public function getSemesterOptionsAction()
    {
        //$this->getHelper('layout')->disableLayout();
        // 篩選傳入值
        $filters = array('*' => 'StringTrim');
        $filterParam = new Zend_Filter_Input($filters,array(),$this->getRequest()->getParams());
        $act = $filterParam->act;

        switch ($act) {
            case 'class_set':
                $q = Doctrine_Query::create()
                ->select('sn, COUNT(sn) AS cc, year, semester')
                ->from('Semester_Model_SemesterClass ')
                ->where('enable=?', 'Y')
                ->groupBy('year, semester');
                break;
            case 'score_setup':
                $q = Doctrine_Query::create()
                ->select('sn, COUNT(sn) AS cc, year, semester')
                ->from('Semester_Model_SemesterExamine')
                ->where('enable=?', 'Y')
                ->groupBy('year, semester');
                break;
            case 'course_setup' :
                $q = Doctrine_Query::create()
                ->select('sn, COUNT(sn) AS cc, year, semester')
                ->from('Semester_Model_SemesterCourse')
                ->where('enable=?', 'Y')
                ->groupBy('year, semester');
                break;

                //                $q = Doctrine_Query::create()
                //                ->select('sn, COUNT(sn) AS cc, year, semester')
                //                ->from('Semester_Model_SemesterSection')
                //                ->where('enable=?', 'Y')
                //                ->groupBy('year, semester');
                //                break;
            case 'teacher_setup':
                $q = Doctrine_Query::create()
                ->select('sn, COUNT(sn) AS cc, year, semester')
                ->from('Semester_Model_SemesterTeacherCourse')
                ->groupBy('year, semester');
                break;
            case 'timetable_setup':
            case 'section_setup':
                $q = Doctrine_Query::create()
                ->select('sn, COUNT(sn) AS cc, year, semester')
                ->from('Semester_Model_SemesterViewSectionTimetable')
                ->groupBy('year, semester');
                break;
            case 'tutor_setup':
                $q = Doctrine_Query::create()
                ->select('sn, COUNT(sn) AS cc, year, semester')
                ->from('Semester_Model_SemesterTeacher')
                ->groupBy('year, semester');
                break;
        }
        $q->orderby('year DESC, semester DESC');
        $arr = array();
        $arr['current'] = '';
        foreach($q->fetchArray() as $row) {
            // 本學期資料
            if ($row['year'] == $this->view->Semester['year'] and $row['semester'] == $this->view->Semester['semester'])
            $arr['current'] = $row;
            else
            $arr['all'][$row['year'].'-'.$row['semester']] =
            sprintf($this->view->translate('%d No. %d academic year semester'),$row['year'], $row['semester'])."({$row['cc']})";
        }

        $this->_helper->json($arr);
    }
}

